package net.yebaihe.wc;

import java.io.File;
import java.util.Date;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.graphics.BitmapFactory;
import android.os.Environment;

class MyHelper extends SQLiteOpenHelper
{
	
	class DishData{
		String name;
	}
	
	public static final String DB_NAME = "wc";
	public static final int DB_VERSION = 8;
	public MyHelper(Context context,String name,CursorFactory factory, int version)
	{
		super(context, name, factory, version);
	}
	@Override
	public void onCreate(SQLiteDatabase arg0) {
		arg0.execSQL("CREATE TABLE IF NOT EXISTS users"
				+" (_id INTEGER PRIMARY KEY, email text,passwd text,username text,lon double,lat double,locatime integer,length double)");		
		arg0.execSQL("CREATE TABLE IF NOT EXISTS restaurants"
				+" (_id INTEGER PRIMARY KEY, name text,city text, style integer, avgprice float,lon double,lat double,discount float,discountdesc text,ordertimes integer,addr text,tel text, length double,zone integer,subzone integer)");		
		arg0.execSQL("CREATE TABLE IF NOT EXISTS dishes"
				+" (_id INTEGER PRIMARY KEY, restaurant integer,name text,style text,price float,ordertimes integer,thumbnail text,imgurl text)");		
		arg0.execSQL("CREATE TABLE IF NOT EXISTS friends"
				+" (_id INTEGER PRIMARY KEY, fromuid integer,touid integer,timestamp integer,friendgroup text)");		
		arg0.execSQL("CREATE TABLE IF NOT EXISTS sessions"
				+" (_id INTEGER PRIMARY KEY, fromuid integer,timestamp integer,title text,restaurant integer,state integer)");		
		arg0.execSQL("CREATE TABLE IF NOT EXISTS sessiondish"
				+" (_id INTEGER PRIMARY KEY, sessionid integer, fromuid integer,timestamp integer,dishid integer,total integer)");		
		arg0.execSQL("CREATE TABLE IF NOT EXISTS sessionuser"
				+" (_id INTEGER PRIMARY KEY, sessionid integer, uid integer,state integer)");	
		arg0.execSQL("CREATE TABLE IF NOT EXISTS notifications"
				+" (_id INTEGER PRIMARY KEY, message text, fromuid integer,touid integer,type integer,userdata text,timestamp integer,done integer)");			
		arg0.execSQL("CREATE TABLE IF NOT EXISTS talk"
				+" (_id INTEGER PRIMARY KEY, cnt text, fromuid integer,sessionid integer,timestamp integer)");			
		arg0.execSQL("CREATE TABLE IF NOT EXISTS favorites"
				+" (_id INTEGER PRIMARY KEY, dishid integer, uid integer,timestamp integer)");			
		arg0.execSQL("CREATE TABLE IF NOT EXISTS favoriter"
				+" (_id INTEGER PRIMARY KEY, restaurant integer, uid integer,timestamp integer)");			
		arg0.execSQL("CREATE TABLE IF NOT EXISTS zone"
				+" (_id INTEGER PRIMARY KEY, name text,city text, ordertimes integer)");			
		arg0.execSQL("CREATE TABLE IF NOT EXISTS subzone"
				+" (_id INTEGER PRIMARY KEY, name text, zone integer, ordertimes integer)");			
		arg0.execSQL("CREATE TABLE IF NOT EXISTS subway"
				+" (_id INTEGER PRIMARY KEY, name text,city text, ordertimes integer)");			
		arg0.execSQL("CREATE TABLE IF NOT EXISTS subway_restaurant"
				+" (_id INTEGER PRIMARY KEY, subway integer, restaurant integer)");			
		arg0.execSQL("CREATE TABLE IF NOT EXISTS style"
				+" (_id INTEGER PRIMARY KEY, name text, ordertimes integer)");			
		arg0.execSQL("CREATE TABLE IF NOT EXISTS style_restaurant"
				+" (_id INTEGER PRIMARY KEY, style integer, restaurant integer)");			
	}
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL("DROP TABLE IF EXISTS users");
		db.execSQL("DROP TABLE IF EXISTS restaurants");
		db.execSQL("DROP TABLE IF EXISTS dishes");
		db.execSQL("DROP TABLE IF EXISTS friends");
		db.execSQL("DROP TABLE IF EXISTS sessions");
		db.execSQL("DROP TABLE IF EXISTS sessiondish");
		db.execSQL("DROP TABLE IF EXISTS sessionuser");
		db.execSQL("DROP TABLE IF EXISTS notifications");
		db.execSQL("DROP TABLE IF EXISTS talk");
		db.execSQL("DROP TABLE IF EXISTS style_restaurant");
		db.execSQL("DROP TABLE IF EXISTS favorites");
		db.execSQL("DROP TABLE IF EXISTS zone");
		db.execSQL("DROP TABLE IF EXISTS subzone");
		db.execSQL("DROP TABLE IF EXISTS subway");
		db.execSQL("DROP TABLE IF EXISTS subway_restaurant");
		db.execSQL("DROP TABLE IF EXISTS style");
		onCreate(db);
	}
	public static MyHelper get(Context ctx) {
		return new MyHelper(ctx,MyHelper.DB_NAME,null,MyHelper.DB_VERSION);
	}
	
	public String getLocalFriendPath(long id) {
		String basedir=Environment.getExternalStorageDirectory()+"/weican/users/";
		File f=new File(basedir);
		if (!f.exists()) f.mkdirs();
		return basedir+id+".dat";
	}

	public UserInfoData getCurrUserInfoData(String email) {
		UserInfoData data=null;
		SQLiteDatabase db = getReadableDatabase();
		if (db!=null){
			Cursor c=db.rawQuery( 
				     "SELECT * FROM users WHERE email='"+
				    		 email+"'", null);
			if (c.moveToFirst()){
				data=new UserInfoData();
				data._id=c.getLong(0);
				data.email=c.getString(1);
				data.username=c.getString(3);
				File f=new File(getLocalFriendPath(data._id));
				if (f.exists()){
					data.image = BitmapFactory.decodeFile(getLocalFriendPath(data._id));
				}
			}
			c.close();
			db.close();
		}
		return data;
	}

	public UserInfoData getCurrUserInfoData(long id) {
		UserInfoData data=null;
		SQLiteDatabase db = getReadableDatabase();
		if (db!=null){
			Cursor c=db.rawQuery( 
				     "SELECT * FROM users WHERE _id="+id, null);
			if (c.moveToFirst()){
				data=new UserInfoData();
				data.email=c.getString(1);
				data.username=c.getString(3);
				data._id=c.getLong(0);
				File f=new File(getLocalFriendPath(data._id));
				if (f.exists()){
					data.image = BitmapFactory.decodeFile(getLocalFriendPath(data._id));
				}
			}
			c.close();
			db.close();
		}
		return data;
	}
	public DishData getDishInfo(long id) {
		DishData data=null;
		SQLiteDatabase db = getReadableDatabase();
		if (db!=null){
			Cursor c=db.rawQuery( 
				     "SELECT * FROM dishes WHERE _id="+id, null);
			if (c.moveToFirst()){
				data=new DishData();
				data.name=c.getString(2);
			}
			c.close();
			db.close();
		}
		return data;
	}
	public int getSessionDishCount(int sessionid) {
		int total=0;
		SQLiteDatabase db = getReadableDatabase();
		if (db!=null){
			Cursor c=db.rawQuery( 
				     "SELECT sum(total) FROM sessiondish WHERE sessionid="+sessionid, null);
			if (c.moveToFirst()){
				total=c.getInt(0);
			}
			c.close();
			db.close();
		}
		return total;
	}
	public boolean insertNotify(long _id,long _id2,String message,int type,String userdata) {
		SQLiteDatabase db = this.getWritableDatabase();
		if (db!=null){
			ContentValues v=new ContentValues();
			v.put("message", message);
			v.put("fromuid", _id);
			v.put("touid", _id2);
			v.put("type", type);
			v.put("userdata", userdata);
			v.put("timestamp", new Date().getTime());
			v.put("done", "0");
			db.insert("notifications", null, v);
			db.close();
		}
		return true;
	}
	public void addFavoriteRestaurant(long rid, long uid) {
		SQLiteDatabase db = this.getWritableDatabase();
		if (db!=null){
			Cursor c=db.rawQuery( 
					"SELECT * FROM favoriter WHERE uid="+uid+" and restaurant="+rid, null);
			if (!c.moveToFirst()){
				ContentValues v=new ContentValues();
				v.put("restaurant", rid);
				v.put("uid", uid);
				v.put("timestamp", new Date().getTime());
				db.insert("favoriter", null, v);
			}
			c.close();
			db.close();
		}
	}
}	

